package com.vogue.supplymall.order.domain.provider;

import com.vogue.ssm.database.provider.BaseSqlProvider;
import com.vogue.ssm.database.util.DealStatus;
import com.vogue.supplymall.common.constant.ConstantBean;
import com.vogue.supplymall.common.domain.dto.SearchOrderInfo;
import org.apache.commons.lang3.StringUtils;

/**
 * The type Cart provider.
 *
 * @aouthor: zhangliang
 * @date: Create in 2018-01-10
 * @version 1.0
 * @version 1.1 所有待接单状态取消6小时不接单算取消的判断
 */
public class OrderListSqlProvider extends BaseSqlProvider {
    /**
     * Find string.
     *
     * @param searchOrderInfo the search order info
     * @return the string
     */
    public String find(SearchOrderInfo searchOrderInfo) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ")
                .append(" o.id orderid, ")
                .append(" o.ordernumber, ")
                .append(" o.createdate ordercreatedate,")
                .append(" o.modifydate ordermodifydate,")//增加订单修改日期，用于倒计时用
                .append(" oss.id statusId, ")
                .append(" oss.pullstate sellpullstate, ")
                // 2018/7/2 添加买家拉取状态
                .append(" oss.buypullstate buypullstate, ")
                .append(" IFNULL(oc.cancelstate, 0) cancelstate, ")
                .append(" o.finalprice  orderprice, ")
                .append(" o.totalquantity, ")
                .append(" o.freight, ")
                .append(" os.organid groupId, ")
                .append(" os.shopId, ")
                .append(" os.entname groupName, ")
                .append(" os.shopName, ")
                .append(" os.mcid, ")
                // 20180905 未预付款查询添加
                .append(" os.syscodeid sellsyscodeid, ")
                .append(" os.organid sellorganid, ")
                .append(" os.stel selltel, ")
                .append(" ob.btel buytel, ")
                .append(" ob.storename buyorganname, ")
                .append(" ob.buyertype, ")
                .append(" CONCAT(oss.orderstatus,'') billState, ")
                .append(" IF((oss.orderstatus=0 AND TIMESTAMPDIFF(MINUTE, o.modifydate,CURTIME())>= ")
                .append(ConstantBean.PAY_FAILURE_TIME.longValue()/60000)
                .append(" )")
//                .append(" OR (oss.orderstatus=1 AND TIMESTAMPDIFF(HOUR, oss.modifydate,CURTIME())>=")
//                .append(ConstantBean.SELLER_AGREE_FAILURE_TIME.longValue()/3600000)
//                .append(")")
                .append(", 1,IF(oss.iscancel,1,0)) iscancel , ")
                .append(" IFNULL( op.paymentmode,'0') paymentmode, ")
                .append(" oip.price itemprice, ")
                .append(" oi.itemquantity, ")
                .append(" oi.storageid, ")
                .append(" oi.`storage` , ")
                .append(" oip.imgurl, ")
                .append(" oip.pname, ")
                .append(" pa.name unitname ");
        sql.append(" FROM ")
                .append(" `order` o ")
                .append(" INNER JOIN order_status oss ON (o.id = oss.orderid) ")
                .append(" LEFT JOIN (select GROUP_CONCAT(paymentmode) paymentmode,orderid from order_payment  group by orderid) op ON (o.id = op.orderid) ")
                .append(" LEFT JOIN (select oc.cancelstate, oc.createdate,oc.orderid from ( ")
                .append(" select oc.orderid,oc.cancelstate,oc.createdate from order_cancel oc order by oc.createdate desc ")
                .append(" ) oc group by oc.orderid) oc ON (o.id = oc.orderid) ")
                .append(" INNER JOIN order_seller os ON (o.id = os.orderid) ")
                .append(" INNER JOIN order_buyer ob ON (o.id = ob.orderid) ")
                .append(" INNER JOIN order_item oi ON (o.id = oi.orderid) ")
                .append(" INNER JOIN order_item_product oip ON ( ")
                .append(" o.id = oip.orderid ")
                .append(" AND oi.id = oip.orderitemid ")
                .append(" ) ")
                .append(" LEFT JOIN product_attrplan_detail pad ON (pad.attrplanid = oip.attrplanid) ")
                .append(" LEFT JOIN product_attrvalue pa ON (pad.attrvalueid = pa.id AND pa.typeid = 1) ");
        sql.append(" INNER JOIN ( ")
                .append(selectOrder(searchOrderInfo))
                .append(" ) o2 ON o.id=o2.id ");
        return DealStatus.formatShadeSql(sql.toString());
    }

    /**
     * Find count string.
     *
     * @param searchOrderInfo the search order info
     * @return the string
     */
    public String findCount(SearchOrderInfo searchOrderInfo) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ")
                .append(" SUM(IF(oss.iscancel,0,IF(oss.orderstatus=0 AND TIMESTAMPDIFF(MINUTE, o.modifydate,CURTIME())<")
                .append(ConstantBean.PAY_FAILURE_TIME.longValue()/60000)
                .append(" ,1,0))) state0, ")
//        	.append(" SUM(IF(oss.iscancel,0,IF(oss.orderstatus=0  ,1,0))) state0, ")
                .append(" SUM(IF(oss.iscancel,0,IF(oss.orderstatus=1 ")
//                .append("AND TIMESTAMPDIFF(HOUR, oss.modifydate,CURTIME())<")
//                .append(ConstantBean.SELLER_AGREE_FAILURE_TIME.longValue()/3600000)
                .append(" ,1,0))) state1, ")
                .append(" SUM(IF(oss.iscancel,0,IF(oss.orderstatus=2 AND oss.pullstate<>1,1,0))) state2, ")
                .append(" SUM(IF(oss.iscancel,0,IF(oss.orderstatus=3,1,0))) state3, ")
                .append(" SUM(IF(oss.iscancel,0,IF(oss.orderstatus=2 AND oss.pullstate=1,1,0))) state4, ")
                
                .append(" SUM(case when ro.id is null then 0 else 1 end) state5 ")
                
                .append(" FROM 	`order` o ")
                .append(" INNER JOIN order_status oss ON (o.id = oss.orderid) ")
                .append(" INNER JOIN order_seller os ON (o.id = os.orderid) ")
                .append(" INNER JOIN order_buyer ob ON (o.id = ob.orderid) ")
                //.append(" left JOIN refund_order ro ON (o.id = ro.orderid) ")
                //.append(" left JOIN refund_status ra ON (ro.id = ra.refundid) and ra.refundstatus in (0,1,4)")
                .append(" LEFT JOIN (SELECT ro.* FROM refund_order ro,refund_status ra WHERE ro.id = ra.refundid AND ra.refundstatus IN (0,1,4) )ro ON o.id = ro.orderid ")
                .append(" WHERE  o.disabledflag = 0  ");
        if (searchOrderInfo.isIfBuyer()) {
            sql.append(" AND ob.syscodeid = #{syscodeid} ")
                    .append(" AND ob.organid = #{organid} ")
                    .append(" AND ob.userid = #{loginId} ");
            
        } else {
            sql.append(" AND os.syscodeid = #{syscodeid} ")
                    .append(" AND os.organid = #{organid} ");
        }


        return DealStatus.formatShadeSql(sql.toString());
    }

    private String selectOrder(SearchOrderInfo searchOrderInfo) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT DISTINCT o.id ")
                .append(" FROM 	`order` o ")
                .append(" INNER JOIN order_status oss ON (o.id = oss.orderid) ")
                .append(" INNER JOIN order_seller os ON (o.id = os.orderid) ")
                .append(" INNER JOIN order_buyer ob ON (o.id = ob.orderid) ")
                .append(" INNER JOIN order_item oi ON (o.id = oi.orderid) ")
                .append(" INNER JOIN order_item_product oip ON ( ")
                .append(" o.id = oip.orderid ")
                .append(" AND oi.id = oip.orderitemid ")
                .append(" ) ")
                .append(" LEFT JOIN (SELECT oc.cancelstate, oc.createdate, oc.orderid  FROM ")
                .append("(SELECT oc.orderid, oc.cancelstate, oc.createdate FROM order_cancel oc ORDER BY oc.createdate DESC) oc ")
                .append(" GROUP BY oc.orderid  ) oc  ON o.id = oc.orderid  ")
                .append(" WHERE o.disabledflag = 0 ");
        if (searchOrderInfo.isIfBuyer()) {
            sql.append(" AND ob.syscodeid = #{syscodeid} ")
                    .append(" AND ob.organid = #{organid} ")
                    .append(" AND ob.userid = #{loginId} ");

        } else {
            sql.append(" AND os.syscodeid = #{syscodeid} ")
                    .append(" AND os.organid = #{organid} ");
        }

        // 匹配搜索内容
        if (StringUtils.isNotEmpty(searchOrderInfo.getKeyword())) {
            sql.append(" AND ( o.ordernumber LIKE CONCAT('%',#{keyword},'%') ")
                    .append(" OR os.shopname LIKE CONCAT('%',#{keyword},'%') ")
                    .append(" OR oip.pname LIKE CONCAT('%',#{keyword},'%') ")
                    .append(" ) ");
        }
        
        //已拉取的订单，在待发货中不能被看到，再备货中可看到
        if(null != searchOrderInfo.getOrderStatus()) {
            if (searchOrderInfo.getOrderStatus() == 2) {
                if (searchOrderInfo.isSellerpull()) {
                    sql.append(" AND oss.pullstate = 1 ");
                } else {
                    sql.append(" AND oss.pullstate <> 1 ");
                }
            }
        }
        
        if (searchOrderInfo.isIsCancel()) {
            sql.append(" AND (oss.iscancel = #{isCancel} OR (oss.orderstatus=0 AND TIMESTAMPDIFF(MINUTE, o.modifydate,CURTIME())>=")
            .append(ConstantBean.PAY_FAILURE_TIME.longValue()/60000)
            .append(" ) ")
//            .append("OR (oss.orderstatus=1 AND TIMESTAMPDIFF(HOUR, oss.modifydate,CURTIME())>=")
//            .append(ConstantBean.SELLER_AGREE_FAILURE_TIME.longValue()/3600000)
//            .append(")")
            .append(")");
        } else if (searchOrderInfo.getOrderStatus() != null) {
            sql.append(" AND oss.orderstatus = #{orderStatus} AND oss.iscancel = 0 ");
            if (searchOrderInfo.getOrderStatus() == 0) {
                sql.append(" AND TIMESTAMPDIFF(MINUTE, o.modifydate,CURTIME())<")
                .append(ConstantBean.PAY_FAILURE_TIME.longValue()/60000);
            }else if(searchOrderInfo.getOrderStatus() == 1){
//            	sql.append(" AND TIMESTAMPDIFF(HOUR, oss.modifydate,CURTIME())<")
//            	.append(ConstantBean.SELLER_AGREE_FAILURE_TIME.longValue()/3600000);
            }
        }
        
        if(searchOrderInfo.getCancelstate() != null && searchOrderInfo.getCancelstate() >= 0) {
        	if(searchOrderInfo.getCancelstate() == 0) {
        		sql.append(" AND (oc.cancelstate = 0 or oc.cancelstate is null) ");
        	}else {
        		sql.append(" AND oc.cancelstate = ").append(searchOrderInfo.getCancelstate());
        	}        	
        }

        //下单开始时间
        if (StringUtils.isNotEmpty(searchOrderInfo.getBeginDate())) {
            sql.append(" AND o.createdate >= #{beginDate} ");
        }

        //下单结束时间
        if (StringUtils.isNotEmpty(searchOrderInfo.getEndDate())) {
           sql.append("  AND o.createdate <= #{endDate}  ");
        }

        sql.append(" ORDER BY oss.modifydate DESC ")
                .append(" LIMIT #{start},#{length} ");

        return sql.toString();
    }
}
